package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import bean.Customer;
import conn.Conn;

public class AdminSql {

	//查询是否有该管理员密码的管理员
	public static boolean isAdmin(String ID, String Password){

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		String type = null;

		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID =" + "'" + ID + "'" + " and Password=" + "'" + Password + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				type = rs.getString("Type");
				return type.equals("admin");
			}
			return false;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return false;
	}

	//根据管理员号查询管理员姓名
	public static String getName(String unumber) {

		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = conn.createStatement();
			String sql = "select Name from customer where ID = " + "'" + unumber +"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getString("Name");
			}
			return null;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}


	//查询所有学生信息
	public static ArrayList<Customer> GetStudent(){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Customer> list = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			String sql="select * from customer where Type = 'student' ";
			rs = stmt.executeQuery(sql);
			int difference = 1;
			while(rs.next()) {
				String ID = rs.getString("ID");
				String Name = rs.getString("Name");
				String Password = rs.getString("Password");
				String Sex = rs.getString("Sex");
				Customer customer = new Customer(ID, Name, Password, Sex ,difference);
				list.add(customer);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}


	//获得所有教师信息
	public static ArrayList<Customer> GetTeacher(){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Customer> list = new ArrayList<>();
		try {
			stmt = conn.createStatement();
			String sql="select * from customer where Type = 'teacher' ";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				String ID = rs.getString("ID");
				String Name = rs.getString("Name");
				String age = rs.getString("age");
				String Password = rs.getString("Password");
				String Sex = rs.getString("Sex");
				Customer customer = new Customer(ID, Name, Password,age, Sex ,1,1);
				list.add(customer);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	//查询特定ID信息
	public static Customer getCustomerByID(String ID) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where ID = "+"'" + ID + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				String Name = rs.getString("Name");
				String Password = rs.getString("Password");
				String Sex = rs.getString("Sex");
				String Type = rs.getString("Type");
				return new Customer(ID, Name, Password, Sex, Type,1);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//查询特定Name信息
	public static Customer getCustomerByName(String Name) {
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			String sql = "select * from customer where Name = "+"'" + Name + "'";
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				String ID = rs.getString("ID");
				String Password = rs.getString("Password");
				String Sex = rs.getString("Sex");
				return new Customer(ID, Name, Password, Sex, 1);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt, rs);
		}
		return null;
	}

	//添加一名人员信息
	public static boolean insertCustomer(String ID, String Name, String Depart,String Sex, String Type){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		String Password = "123456";
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "insert into customer(ID, Name, Depart, Sex, Password ,Type ) values (" + "'" + ID + "'" + "," + " '" + Name + "'" + "," + " '" + Depart + "'" + "," + " '" + Sex + "'" + "," + " '" + Password + "'" + "," + " '" + Type + "'" + ")";
			a = stmt.executeUpdate(sql);
			return a > 0;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}



	//根据ID删除信息
	public static void removeCustomer(String ID){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "delete from customer where ID = " + "'" + ID + "'";
//			如果需要同时删除其他表中相关信息
//			String sql2 = "delete from score where ID = " + "'" + ID + "'";
//			String sql3 = "delete from grade_design where ID = " + "'" + ID + "'";
			stmt.executeUpdate(sql);

		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
	}

//更新用户信息
	public static boolean updateCustomer(String ID, String Name,String Password,String Depart,String Sex){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set Name = '" + Name + "',Password = '" +  Password + "',Depart = '" + Depart + "',Sex = '"  + Sex + " 'where ID = '"  + ID + "'";
			stmt.executeUpdate(sql);
			return true;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}

	//更新密码
	public static boolean updatePassword(String ID, String Passord){
		Connection conn = Conn.getConnection();
		Statement stmt = null;
		int a = 0;
		try {
			stmt = conn.createStatement();
			String sql = "update customer set password = " + "'" + Passord + "'" +  " where ID = " + "'" + ID + "'";
			a = stmt.executeUpdate(sql);
			return a > 0;
		}catch(SQLException e){
			e.printStackTrace();
		}finally {
			Conn.release(conn, stmt);
		}
		return false;
	}
}
